
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 04/14/2010 16:52:06
-- Generated from EDMX file: C:\Projects\MVVMQuiz\QuizLib\Model1.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SmartieFly];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Pages'
CREATE TABLE [dbo].[Pages] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [PageNumber] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [IsTitleVisible] nvarchar(max)  NOT NULL,
    [Quiz_Id] int  NOT NULL
);
GO

-- Creating table 'Quizs'
CREATE TABLE [dbo].[Quizs] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL
);
GO

-- Creating table 'QuestionHistories'
CREATE TABLE [dbo].[QuestionHistories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [WasCorrect] nvarchar(max)  NOT NULL,
    [Question_Id] int  NOT NULL,
    [QuizHistory_Id] int  NOT NULL
);
GO

-- Creating table 'Categories'
CREATE TABLE [dbo].[Categories] (
    [Id] int IDENTITY(1,1) NOT NULL
);
GO

-- Creating table 'Modules'
CREATE TABLE [dbo].[Modules] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [ContentId] nvarchar(max)  NOT NULL,
    [Order] nvarchar(max)  NOT NULL,
    [Page_Id] int  NOT NULL
);
GO

-- Creating table 'SubjectMatters'
CREATE TABLE [dbo].[SubjectMatters] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Category_Id] int  NOT NULL
);
GO

-- Creating table 'Questions'
CREATE TABLE [dbo].[Questions] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [SubjectMatter_Id] int  NULL,
    [Category_Id] int  NOT NULL
);
GO

-- Creating table 'QuizHistories'
CREATE TABLE [dbo].[QuizHistories] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [UserId] nvarchar(max)  NOT NULL,
    [Date] nvarchar(max)  NOT NULL,
    [PercentCorrect] nvarchar(max)  NOT NULL,
    [Quiz_Id] int  NOT NULL
);
GO

-- Creating table 'Answers'
CREATE TABLE [dbo].[Answers] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [Question_Id] int  NOT NULL
);
GO

-- Creating table 'Resources'
CREATE TABLE [dbo].[Resources] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [Type] nvarchar(max)  NOT NULL,
    [Blob] nvarchar(max)  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Pages'
ALTER TABLE [dbo].[Pages]
ADD CONSTRAINT [PK_Pages]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Quizs'
ALTER TABLE [dbo].[Quizs]
ADD CONSTRAINT [PK_Quizs]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'QuestionHistories'
ALTER TABLE [dbo].[QuestionHistories]
ADD CONSTRAINT [PK_QuestionHistories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Categories'
ALTER TABLE [dbo].[Categories]
ADD CONSTRAINT [PK_Categories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Modules'
ALTER TABLE [dbo].[Modules]
ADD CONSTRAINT [PK_Modules]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'SubjectMatters'
ALTER TABLE [dbo].[SubjectMatters]
ADD CONSTRAINT [PK_SubjectMatters]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [PK_Questions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'QuizHistories'
ALTER TABLE [dbo].[QuizHistories]
ADD CONSTRAINT [PK_QuizHistories]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [PK_Answers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Resources'
ALTER TABLE [dbo].[Resources]
ADD CONSTRAINT [PK_Resources]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [Quiz_Id] in table 'Pages'
ALTER TABLE [dbo].[Pages]
ADD CONSTRAINT [FK_QuizPage]
    FOREIGN KEY ([Quiz_Id])
    REFERENCES [dbo].[Quizs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuizPage'
CREATE INDEX [IX_FK_QuizPage]
ON [dbo].[Pages]
    ([Quiz_Id]);
GO

-- Creating foreign key on [SubjectMatter_Id] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [FK_SubjectMatterQuestion]
    FOREIGN KEY ([SubjectMatter_Id])
    REFERENCES [dbo].[SubjectMatters]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SubjectMatterQuestion'
CREATE INDEX [IX_FK_SubjectMatterQuestion]
ON [dbo].[Questions]
    ([SubjectMatter_Id]);
GO

-- Creating foreign key on [Category_Id] in table 'SubjectMatters'
ALTER TABLE [dbo].[SubjectMatters]
ADD CONSTRAINT [FK_CategorySubjectMatter]
    FOREIGN KEY ([Category_Id])
    REFERENCES [dbo].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategorySubjectMatter'
CREATE INDEX [IX_FK_CategorySubjectMatter]
ON [dbo].[SubjectMatters]
    ([Category_Id]);
GO

-- Creating foreign key on [Category_Id] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [FK_CategoryQuestion]
    FOREIGN KEY ([Category_Id])
    REFERENCES [dbo].[Categories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryQuestion'
CREATE INDEX [IX_FK_CategoryQuestion]
ON [dbo].[Questions]
    ([Category_Id]);
GO

-- Creating foreign key on [Page_Id] in table 'Modules'
ALTER TABLE [dbo].[Modules]
ADD CONSTRAINT [FK_PageModule]
    FOREIGN KEY ([Page_Id])
    REFERENCES [dbo].[Pages]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_PageModule'
CREATE INDEX [IX_FK_PageModule]
ON [dbo].[Modules]
    ([Page_Id]);
GO

-- Creating foreign key on [Question_Id] in table 'QuestionHistories'
ALTER TABLE [dbo].[QuestionHistories]
ADD CONSTRAINT [FK_QuestionQuestionHistory]
    FOREIGN KEY ([Question_Id])
    REFERENCES [dbo].[Questions]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionQuestionHistory'
CREATE INDEX [IX_FK_QuestionQuestionHistory]
ON [dbo].[QuestionHistories]
    ([Question_Id]);
GO

-- Creating foreign key on [Quiz_Id] in table 'QuizHistories'
ALTER TABLE [dbo].[QuizHistories]
ADD CONSTRAINT [FK_QuizQuizHistory]
    FOREIGN KEY ([Quiz_Id])
    REFERENCES [dbo].[Quizs]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuizQuizHistory'
CREATE INDEX [IX_FK_QuizQuizHistory]
ON [dbo].[QuizHistories]
    ([Quiz_Id]);
GO

-- Creating foreign key on [QuizHistory_Id] in table 'QuestionHistories'
ALTER TABLE [dbo].[QuestionHistories]
ADD CONSTRAINT [FK_QuizHistoryQuestionHistory]
    FOREIGN KEY ([QuizHistory_Id])
    REFERENCES [dbo].[QuizHistories]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuizHistoryQuestionHistory'
CREATE INDEX [IX_FK_QuizHistoryQuestionHistory]
ON [dbo].[QuestionHistories]
    ([QuizHistory_Id]);
GO

-- Creating foreign key on [Question_Id] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [FK_QuestionAnswer]
    FOREIGN KEY ([Question_Id])
    REFERENCES [dbo].[Questions]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionAnswer'
CREATE INDEX [IX_FK_QuestionAnswer]
ON [dbo].[Answers]
    ([Question_Id]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------